package com.central.apps.controller;

import com.central.app.service.HandlerService;
import com.central.common.auth.details.LoginAppUser;
import com.central.common.exception.business.BusinessException;
import com.central.common.exception.service.ServiceException;
import com.central.common.util.SysUserUtil;
import com.central.common.web.Result;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author mengweitao
 * @Date 2022/11/18/018 14:10
 * 采购管理-采购申请申请行导入，模板导出
 * 询价单-供应商报价信息导入，模板导出
 */
@Log4j2
@RestController
@RequestMapping("/purmanage")
public class PurManageController {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    @Autowired
    private HandlerService handlerService;
    @Autowired
    private PurappexlsController purappexlsController;

    /**
     * 供应商报价信息模板导出
     * @param response
     * @throws IOException
     */
    @PostMapping("/inquiryExportExcel")
    public void inquiryExportExcel(HttpServletResponse response) throws IOException {
        try {
            //1.创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFFont redFont = wb.createFont();
            redFont.setColor(Font.COLOR_RED);
            HSSFCellStyle style = wb.createCellStyle();
            style.setFont(redFont);

            HSSFSheet sheet = wb.createSheet("Sheet1");
            HSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("供应商编号");
            row.createCell(1).setCellValue("供应商名称");
            HSSFCell cell = row.createCell(2);
            cell.setCellStyle(style);
            cell.setCellValue("物料编码*");
            row.createCell(3).setCellValue("物料名称");
            row.createCell(4).setCellValue("计量单位");
//            row.createCell(4).setCellValue("数量");
            row.createCell(5).setCellValue("单价");
            row.createCell(6).setCellValue("数量");
//            row.createCell(6).setCellValue("金额");//金额是单价和数量算出来的。不用填写
            row.createCell(7).setCellValue("供货周期(天)");
            row.createCell(8).setCellValue("付款周期(天)");
            row.createCell(9).setCellValue("质保期(月)");
            row.createCell(10).setCellValue("其他");
            row.createCell(11).setCellValue("是否满足技术指标");
            row.createCell(12).setCellValue("是否在预算内");
            row.createCell(13).setCellValue("是否在供货周期内");


            String sfxl ="SFXL";  //是否下拉框
            Map<String, String> sfxlMap = new HashMap<>();
            sfxlMap.put("SFXL","是,否");
            HSSFDataValidation dataValidation = createBox(sfxl, sfxlMap, 1, 10000, 11, 13);
            if (dataValidation != null) {
                sheet.addValidationData(dataValidation);
            }
            //输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            String exportFileName ="供应商列表报价信息.xls";
            String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            response.setContentType("application/x-xls");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 供应商报价信息导入
     * @param myfile
     * @throws Exception
     */
    @PostMapping("/inquiryImportExcel")
    public Result inquiryImportExcel(@RequestParam("file") MultipartFile myfile, @RequestParam("id") String id) throws Exception {
        try {
            //id是该条询价单的id
            List<Map<String, Object>> bySql3 = handlerService.findBySql("select * from gq_inquirysheet where id = '" + id + "'");

            if(StringUtils.isBlank(id) && CollectionUtils.isNotEmpty(bySql3)){
                return Result.failed("未选择询价单id");
            }
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new HSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                return Result.failed("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("Sheet1");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                return Result.failed("请填写数据");
            }


            Map mapObjectHashMap = new HashMap<Map<String, Object>,Object>();
            //检验单元格格式是否正确
            List<Map<String,Object>> resuleList = new ArrayList<>();
            for (int i = 0; i <= rows; i++) {
                Map<String, Object> map1 = new HashMap<>();
                //读取左上端单元格
                Row row = sheet1.getRow(i);
                if (row != null) {
                    //读取excel
                    String suppliernum = getCellValue(row, 0).toString().trim();//供应商编号
                    String suppliername = getCellValue(row, 1).toString().trim();//供应商名称
                    String itemnum = getCellValue(row, 2).toString().trim();//物料编码*
                    String itemdesc = getCellValue(row, 3).toString().trim();//物料名称
                    String orderunit = getCellValue(row, 4).toString().trim();//计量单位
                    String unitcost = getCellValue(row, 5).toString().trim();//单价
                    String quantity = getCellValue(row, 6).toString().trim();//数量
//                    String totalcost = getCellValue(row, 2).toString().trim();//金额
                    String supplyperiod = getCellValue(row, 7).toString().trim();//供货周期（天）
                    String paymentperiod = getCellValue(row, 8).toString().trim();//付款周期(文字格式)
                    String warrantyperiod = getCellValue(row, 9).toString().trim();//质保期(文字格式）
                    String other = getCellValue(row, 10).toString().trim();//其他
                    String technicalindicators = getCellValue(row, 11).toString().trim();//是否满足技术指标
                    String budget = getCellValue(row, 12).toString().trim();//是否在预算内
                    String issupplyperiod = getCellValue(row, 13).toString().trim();//是否在供货周期内
                    //判断模板标头是否正确
                    if (i == 0) {
                        if (!"供应商编号".equals(suppliernum) || !"物料编码*".equals(itemnum) || !"物料名称".equals(itemdesc)
                                || !"计量单位".equals(orderunit)  || !"单价".equals(unitcost) || !"供货周期(天)".equals(supplyperiod)
                            || !"其他".equals(other) || !"是否满足技术指标".equals(technicalindicators) || !"是否在预算内".equals(budget)
                            || !"是否在供货周期内".equals(issupplyperiod) || !"数量".equals(quantity) || !"付款周期(天)".equals(paymentperiod)
                            || !"质保期(月)".equals(warrantyperiod)) {
                            return Result.failed("导入的模板错误，请下载模板进行导入");
                        }
                    }else{

                        //判断导入的物料是否为该询价单
                        List<String> itemList = new ArrayList<>();
                        List<Map<String, Object>> bySql1 = handlerService.findBySql("select itemnum from gq_inquirysheetline where isid = " + id);
                        if(CollectionUtils.isNotEmpty(bySql1)){
                            for (Map<String, Object> map : bySql1) {
                                itemList.add(MapUtils.getString(map,"itemnum"));
                            }
                        }
                        if(CollectionUtils.isNotEmpty(itemList)){
                            int line = i+1;
                            if(!itemList.contains(itemnum)){
                                return Result.failed("第"+line+"行的物料编码没有在询价单号为"+MapUtils.getString(bySql3.get(0),"isnum")+"的询价物料中。");
                            }
                        }

                        List<Map<String, Object>> bySql = handlerService.findBySql("select isnum from gq_inquirysheet where id = '" + id + "'");
                        if(CollectionUtils.isNotEmpty(bySql)){
                            Map<String, Object> map2 = bySql.get(0);
                            map1.put("isnum",MapUtils.getString(map2,"isnum"));
                        }

                        List<Map<String, Object>> bySql2 = handlerService.findBySql("select id from gq_account where supplierno = '" + suppliernum + "'");
                        if(CollectionUtils.isNotEmpty(bySql2)){
                            map1.put("issupplierid",MapUtils.getString(bySql2.get(0),"id"));
                        }

                        List<Map<String, Object>> bySql7 = handlerService.findBySql("select suppliername from gq_account where supplierno = '" + suppliernum + "'");
                        if(CollectionUtils.isNotEmpty(bySql7)){
                            map1.put("suppliername",MapUtils.getString(bySql7.get(0),"suppliername"));//供应商名称
                        }
                        map1.put("suppliernum",suppliernum);//供应商编号


                        List<Map<String, Object>> bySql5 = handlerService.findBySql("select id from gq_inquirysheetline where itemnum = '" + itemnum + "' and isid = '" + id + "'");
                        if(CollectionUtils.isNotEmpty(bySql5)){
                            //同一供应商对同一询价物料的相同数量只允许报一次价
                            HashMap<String, Object> map2 = new HashMap<>();
                            map2.put(MapUtils.getString(bySql5.get(0),"id"),suppliernum);
                            Object quantityNew = mapObjectHashMap.get(map2);
                            if(quantityNew!=null){
                                if(quantity.equals(quantityNew.toString())){
                                    int line = i+1;
                                    return Result.failed("第"+line+"行该供应商对同一询价物料的相同数量只允许报一次价");
                                }
                            }
                            List<Map<String, Object>> bySql6 = handlerService.findBySql("select * from gq_isoffer where inquiryitemid = '" + MapUtils.getString(bySql5.get(0),"id") + "' and itemnum = '"
                                    + itemnum + "' and quantity = '" + quantity + "' and suppliernum = '" + suppliernum + "'");
                            if(CollectionUtils.isNotEmpty(bySql6)){
                                int line = i+1;
                                return Result.failed("第"+line+"行该供应商对同一询价物料的相同数量只允许报一次价");
                            }
                            map1.put("inquiryitemid",MapUtils.getString(bySql5.get(0),"id"));
                            HashMap<String, Object> map = new HashMap<>();
                            map.put(MapUtils.getString(bySql5.get(0),"id"),suppliernum);
                            mapObjectHashMap.put(map,quantity);
                        }else{
                            int line = i+1;
                            return Result.failed("第"+line+"行的物料编码没有在询价单号为"+MapUtils.getString(bySql3.get(0),"isnum")+"的询价物料中。");
                        }

                        map1.put("isid",id);//询价单id
                        map1.put("other",other);
                        map1.put("issupplyperiod",issupplyperiod);
                        map1.put("supplyperiod",supplyperiod);
                        map1.put("budget",budget);
                        map1.put("technicalindicators",technicalindicators);


                        List<Map<String, Object>> bySql4 = handlerService.findBySql("select * from gq_item where itemnum = '" + itemnum + "'");
                        if(CollectionUtils.isNotEmpty(bySql4)){
                            Map<String, Object> map = bySql4.get(0);
                            map1.put("itemdesc", MapUtils.getString(map,"description"));//物料名称
                            map1.put("orderunit", MapUtils.getString(map,"unit"));//计量单位
                        }
                        map1.put("itemnum", itemnum);//物料编码
                        map1.put("quantity", quantity);//数量

                        //根据数量单价算金额  金额=数量*单价
                        BigDecimal unitcostBig = new BigDecimal(unitcost);
                        BigDecimal quantityBig= new BigDecimal(quantity);
                        BigDecimal totalcost = unitcostBig.multiply(quantityBig);
                        map1.put("unitcost",unitcost);//单价
                        map1.put("totalcost",totalcost.toString());//金额

                        map1.put("warrantyperiod",warrantyperiod);//质保期(月)
                        map1.put("paymentperiod",paymentperiod);//付款周期(月)
                        //时间，人
                        LoginAppUser user = SysUserUtil.getLoginAppUser();
                        map1.put("createuser",user.getId());
                        map1.put("updateuser",user.getId());
                        map1.put("updatetime",new Date());
                        map1.put("createtime",new Date());
                        resuleList.add(map1);
                    }
                }
            }
            if(resuleList!=null && resuleList.size()>0){
                //批量插入数据
                handlerService.batchInsert("inquirysheetApp","gq_isoffer",resuleList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Result.succeed("导入成功");
    }

    /**
     * 采购信息子表-采购申请行模板导出
     * @param response
     * @throws IOException
     */
    @PostMapping("/purRequisLineExportExcel")
    public void purRequisLineExportExcel(HttpServletResponse response) throws IOException {
        try {
            //1.创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Sheet1");
            //说明
            HSSFRow rowzero = sheet.createRow(0);
            rowzero.setHeight((short) 1500);

            HSSFFont redFont = wb.createFont();
            redFont.setColor(Font.COLOR_RED);
            HSSFCellStyle style = wb.createCellStyle();
            HSSFCellStyle style1 = wb.createCellStyle();
            style.setFont(redFont);
            style.setWrapText(true);
            style.setAlignment(HorizontalAlignment.LEFT);// 左右居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

            //设置固定列的单元格格式
            HSSFDataFormat format = wb.createDataFormat();
            style1.setDataFormat(format.getFormat("yyyy/m/d"));


            HSSFCell cell = rowzero.createCell(0);
            cell.setCellStyle(style);
            cell.setCellValue("说明："+"\r\n"+"1.请将鼠标移动到灰色标题行查看字段录入要求"
                    +"\r\n"+"2.红色带星号（*）的字段为必填字段"
                    +"\r\n"+"3.第5行为采购明细行对应建议供应商数据，以物料编码为关联信息");

            // 合并第一行 从第一行开始第一行结束，第0列开始 第cellNum列结束
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0, 4);
            sheet.addMergedRegion(callRangeAddress);

            HSSFRow row2 = sheet.createRow(2);
            HSSFCell cell8 = row2.createCell(5);
            cell8.setCellStyle(style1);

            HSSFRow row = sheet.createRow(1);
            //给第二行里面添数据
            HSSFCell cell1 = row.createCell(0);
            cell1.setCellStyle(style);
            cell1.setCellValue("*采购类别");
            HSSFCell cell2 = row.createCell(1);
            this.setCommentsOfPoiCell(5,5,cell2,row,"物料编码为文本字段，11位，由数字和字母组成");
            cell2.setCellStyle(style);
            cell2.setCellValue("*物料编码");
            HSSFCell cell3 = row.createCell(2);
            cell3.setCellStyle(style);
            cell3.setCellValue("*需求数量");
            HSSFCell cell4 = row.createCell(3);
            cell4.setCellStyle(style);
            cell4.setCellValue("*单价");
            HSSFCell cell9 = row.createCell(4);
            cell9.setCellStyle(style);
            cell9.setCellValue("*购置理由");
            HSSFCell cell5 = row.createCell(5);
            this.setCommentsOfPoiCell(2,2,cell5,row,"时间格式为yyyy/m/d,例如：2022/11/30");
            cell5.setCellStyle(style);
            cell5.setCellValue("*要求到货日期");
            HSSFCell cell7 = row.createCell(6);
            cell7.setCellStyle(style);
            cell7.setCellValue("*紧急程度");
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellStyle(style);
            cell6.setCellValue("*是否本人占用");
            HSSFCell cell10 = row.createCell(8);
            cell10.setCellStyle(style);
            cell10.setCellValue("*采购属性");
            HSSFCell cell11 = row.createCell(9);
            cell11.setCellStyle(style);
            cell11.setCellValue("*采购渠道");
            HSSFCell cell12 = row.createCell(10);
            cell12.setCellStyle(style);
            cell12.setCellValue("*采购方式");
            row.createCell(11).setCellValue("电商采购平台");
            row.createCell(12).setCellValue("电商货号");
            row.createCell(13).setCellValue("是否涉及四新");
            row.createCell(14).setCellValue("是否用于危险作业");

            row.createCell(15).setCellValue("验收要求-点验数量、外观、型号");
            row.createCell(16).setCellValue("验收要求-查验合格证、出厂质量报告等");
            row.createCell(17).setCellValue("验收要求-查验第三方检验报告");
            row.createCell(18).setCellValue("验收要求-技术指标验证");
            row.createCell(19).setCellValue("验收要求-入所质检");

            row.createCell(20).setCellValue("元器件检验技术条件");
            row.createCell(21).setCellValue("是否DPA分析");
            row.createCell(22).setCellValue("特殊要求(DPA)");
            row.createCell(23).setCellValue("是否特制");
            row.createCell(24).setCellValue("是否赴厂验收");
            row.createCell(25).setCellValue("是否送检");
            row.createCell(26).setCellValue("入所检测要求");

            sheet.setColumnWidth(16,sheet.getColumnWidth(16)*17/5);
            sheet.setColumnWidth(17,sheet.getColumnWidth(17)*17/5);
            sheet.setColumnWidth(18,sheet.getColumnWidth(18)*17/5);
            sheet.setColumnWidth(19,sheet.getColumnWidth(19)*17/10);
            sheet.setColumnWidth(20,sheet.getColumnWidth(20)*17/10);
            sheet.setColumnWidth(0,sheet.getColumnWidth(0)*17/8);


            // 添加供应商行标题
            HSSFRow supplierTitleRow = sheet.createRow(4);
            HSSFCell stcell1 = supplierTitleRow.createCell(0);
            stcell1.setCellStyle(style);
            stcell1.setCellValue("供应商列表");
            // 合并第一行 从第一行开始第一行结束，第0列开始 第cellNum列结束
            callRangeAddress = new CellRangeAddress(4,4,0, 4);
            sheet.addMergedRegion(callRangeAddress);

            // 添加供应商行标题
            HSSFRow supplierRow = sheet.createRow(5);
            //给第二行里面添数据
            HSSFCell scell1 = supplierRow.createCell(0);
//            scell1.setCellStyle(style);
            scell1.setCellValue("物料编码");
            supplierRow.createCell(1).setCellValue("建议供应商编号");
            HSSFCell scell2 = supplierRow.createCell(2);
//            scell2.setCellStyle(style);
            scell2.setCellValue("建议供应商名称");
            supplierRow.createCell(3).setCellValue("联系人");
            supplierRow.createCell(4).setCellValue("联系电话");
//            supplierRow.createCell(4).setCellValue("购置理由");

            //下拉框字段设置
            String wllx="WLLX"; //物料类型
            Map<String, String> wllxmap = new HashMap<>();
            wllxmap.put("WLLX","A1类(实验用),A2类(设备仪器),A4类(食用),A5类(民用)");  //物料类型
            //指定将下拉框添加至1-10000行，0-0列。即第一列的第2到10001行
            HSSFDataValidation dataValidation = createBox(wllx, wllxmap, 2,4 , 0, 0);
            if (dataValidation != null) {
                sheet.addValidationData(dataValidation);
            }

            String jjcd="JJCD"; //紧急程度
            Map<String, String> jjcdmap = new HashMap<>();
            jjcdmap.put("JJCD","紧急,一般");
            //指定将下拉框添加至1-10000行，0-0列。即第一列的第2到10001行
            HSSFDataValidation dataValidation7 = createBox(jjcd, jjcdmap, 2,4 , 6, 6);
            if (dataValidation7 != null) {
                sheet.addValidationData(dataValidation7);
            }

            String sjsx ="SJSX";  //是否涉及四新/危险作业
            Map<String, String> sjsxMap = new HashMap<>();
            sjsxMap.put("SJSX","是,否");
            HSSFDataValidation dataValidation1 = createBox(sjsx, sjsxMap, 2, 4, 13, 14);
            HSSFDataValidation dataValidation2 = createBox(sjsx, sjsxMap, 2, 4, 7, 7);
            if (dataValidation1 != null) {
                sheet.addValidationData(dataValidation1);
            }
            if (dataValidation2 != null) {
                sheet.addValidationData(dataValidation2);
            }

            String brzy ="BRZY";  //是否本人占用
            Map<String, String> brzyMap = new HashMap<>();
            brzyMap.put("SJSX","是,否");
            HSSFDataValidation dataValidation19 = createBox(brzy, brzyMap, 2, 4, 7, 7);
            if (dataValidation19 != null) {
                sheet.addValidationData(dataValidation19);
            }


            String sfsj = "SFSJ";   //是否送检/是否特制/是否赴厂验收
            Map<String, String> sfsjMap = new HashMap<>();
            sfsjMap.put("SFSJ","是,否");
            HSSFDataValidation dataValidation3 = createBox(sfsj, sfsjMap, 2, 4, 23, 25);
            if (dataValidation3 != null) {
                sheet.addValidationData(dataValidation3);
            }

            String ysyq = "YSYQ";   //验收要求
            Map<String, String> ysyq1Map = new HashMap<>();
            ysyq1Map.put("YSYQ","是,否");
            HSSFDataValidation dataValidation6 = createBox(ysyq, ysyq1Map, 2, 4, 15, 19);
            if (dataValidation3 != null) {
                sheet.addValidationData(dataValidation6);
            }

            //采购属性
            List<Map<String, Object>> bySql = handlerService.findBySql("select name from sys_dict where pid in (select id from sys_dict where code = 'pp_projecttype')");
            Map<String, String> cgsxmap = new HashMap<>();
            StringJoiner stringJoiner = new StringJoiner(",");
            for (Map<String, Object> stringObjectMap : bySql) {
                String name = MapUtils.getString(stringObjectMap, "name");
                stringJoiner.add(name);
            }
            cgsxmap.put("cgsx",stringJoiner.toString());
            if(CollectionUtils.isNotEmpty(bySql)){
                String cgsx="cgsx";
                HSSFDataValidation dataValidation4 = createBox(cgsx, cgsxmap, 2, 4, 8, 8);
                if (dataValidation4 != null) {
                    sheet.addValidationData(dataValidation4);
                }
            }

            //采购渠道
            List<Map<String, Object>> bySql1 = handlerService.findBySql("select  name from sys_dict where pid in (select id from sys_dict where code = 'po_purchaseway')");
            StringJoiner stringJoiner1 = new StringJoiner(",");
            Map<String, String> cgqdmap = new HashMap<>();
            for (Map<String, Object> map : bySql1) {
                String name = MapUtils.getString(map, "name");
                stringJoiner1.add(name);
            }
            cgqdmap.put("cgqd",stringJoiner1.toString());
            if(CollectionUtils.isNotEmpty(bySql1)){
                String cgsx="cgqd";
                HSSFDataValidation dataValidation4 = createBox(cgsx, cgqdmap, 2, 4, 9, 9);
                if (dataValidation4 != null) {
                    sheet.addValidationData(dataValidation4);
                }
            }

            String cgfs = "CGFS";   //采购方式
            Map<String, String> boxMap = new HashMap<>();
            List<Map<String, Object>> bySql2 = handlerService.findBySql("select name from sys_dict where pid in (select id from sys_dict where code = 'po_purchasetype')");
            StringJoiner stringJoiner2 = new StringJoiner(",");
            if(CollectionUtils.isNotEmpty(bySql2)){
                for (Map<String, Object> map : bySql2) {
                    stringJoiner2.add(MapUtils.getString(map,"name"));
                }
            }

            boxMap.put("CGFS", stringJoiner2.toString());
            HSSFDataValidation dataValidation4 = createBox(cgfs, boxMap, 2, 4, 10, 10);
            if (dataValidation4 != null) {
                sheet.addValidationData(dataValidation4);
            }

            String dscg="DSCG";  //是否为电商采购
            Map<String, String> dscgMap = new HashMap<>();
            List<Map<String, Object>> bySql3 = handlerService.findBySql("select name from sys_dict where pid in (select id from sys_dict where code = 'wheprocuremen')");
            StringJoiner stringJoiner3 = new StringJoiner(",");
            if(CollectionUtils.isNotEmpty(bySql3)){
                for (Map<String, Object> map : bySql3) {
                    stringJoiner3.add(MapUtils.getString(map,"name"));
                }
            }
            dscgMap.put("DSCG",stringJoiner3.toString());
            HSSFDataValidation dataValidation5 = createBox(dscg, dscgMap, 2, 4, 11, 11);
            if (dataValidation5 != null) {
                sheet.addValidationData(dataValidation5);
            }

            String dpa = "DPA";   //是否DPA分析
            Map<String, String> dpaMap = new HashMap<>();
            dpaMap.put("DPA","是,否");
            HSSFDataValidation dataValidation8 = createBox(dpa, dpaMap, 2, 4, 21, 21);
            if (dataValidation8 != null) {
                sheet.addValidationData(dataValidation8);
            }

           //输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            String exportFileName ="采购申请行导入模板.xls";
            String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            response.setContentType("application/x-xls");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 采购申请明细行信息导入
     * @param myfile
     * @throws Exception
     */
    @PostMapping("/purRequisLineImportExcel")
    @Transactional(rollbackFor = Exception.class)
    public Map purRequisLineImportExcel(@RequestParam("file") MultipartFile myfile,@RequestParam("id") String id) throws Exception {
        Map<String, Object> map3 = new HashMap<>();
        String itemnum = "";
        String description = "";
        String specifications = "";
        String model = "";
        Boolean businessExceptionStr = false;//检验模板是否有系统不存在的物料主数据
        List<Map<String, Object>> noexisItemList = new ArrayList<>();
        try {
            //判断是否传父表--供应商id
            if(StringUtils.isBlank(id)){
                throw new ServiceException("未选择一条采购申请");
            }
            List<Map<String, Object>> bySql4 = handlerService.findBySql("select * from gq_pr where id = '" + id + "'");
            String applyby = "";
            String subjectno = "";
            if(CollectionUtils.isNotEmpty(bySql4)){
                Map<String, Object> map = bySql4.get(0);
                applyby = MapUtils.getString(map, "applyby");
                subjectno = MapUtils.getString(map, "subjectno");
            }
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new HSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                throw new ServiceException("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("Sheet1");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                throw new ServiceException("请填写数据");
            }

            List<Map<String,Object>> itemList = new ArrayList<>();
            //采购申请行里的物料编码不能重复导入。过滤掉
            HashSet<String> itemnumSet = new HashSet<>();
            boolean flag = false;
            boolean judge = true;
            for (int i = 3; i <= rows; i++) {
                Map<String, Object> resultMap = new HashMap<>();
                Map<String, Object> noexisItem = new HashMap<>();
                //读取左上端单元格
                Row row = sheet1.getRow(i);
                //行不为空
                boolean rowNull = checkNullRow(row);
                if (rowNull == false) {
                    //读取excel
                    String itemtype = getCellValue(row, 0).toString().trim();//*采购类别
                    itemnum = getCellValue(row, 1).toString().trim();//物料编码
                    description = getCellValue(row, 2).toString().trim();//*物料名称
                    model = getCellValue(row, 3).toString().trim();//*型号
                    specifications = getCellValue(row, 4).toString().trim();//规格
                    String orderqty = getCellValue(row, 5).toString().trim();//*需求数量
                    String unitcost = getCellValue(row, 6).toString().trim();//*预算单价(元)

                    String reasonpur = getCellValue(row, 7).toString().trim();//*购置理由
                    String reqdeliverydate = getCellValue(row, 8).toString().trim();//*要求到货日期
                    String urgency = getCellValue(row, 9).toString().trim();//*紧急程度
                    String occupnot = getCellValue(row, 10).toString().trim();//*是否本人占用
                    String projecttype   = getCellValue(row, 11).toString().trim();//*采购属性
                    String purchaseway = getCellValue(row, 12).toString().trim();//*采购渠道
                    String purchasemethod = getCellValue(row, 13).toString().trim();//*采购方式
                    String wheprocuremen = getCellValue(row, 14).toString().trim();//电商采购平台
                    String commerceorder = getCellValue(row, 15).toString().trim();//建议电商货号
                    String advisupplier = getCellValue(row, 16).toString().trim();//建议供应商
                    String whfournew = getCellValue(row, 17).toString().trim();//*是否涉及四新
                    String whethdangerous = getCellValue(row, 18).toString().trim();//*是否用于危险作业

                    String acceptments = getCellValue(row, 19).toString().trim();//验收要求-点验数量、外观、型号
                    String acceptments1 = getCellValue(row, 20).toString().trim();//验收要求-查验合格证、出厂质量报告等
                    String acceptments2 = getCellValue(row, 21).toString().trim();//验收要求-查验第三方检验报告
                    String acceptments3 = getCellValue(row, 22).toString().trim();//验收要求-技术指标验证
                    String acceptments4 = getCellValue(row, 23).toString().trim();//验收要求-入所质检

                    String componentsparts = getCellValue(row, 24).toString().trim();//元器件检验技术条件
                    String isdpaanalysis = getCellValue(row, 25).toString().trim();//是否DPA分析
                    String specialrequirements = getCellValue(row, 26).toString().trim();//特殊要求(DPA)
                    String isspeciallymade = getCellValue(row, 27).toString().trim();//是否特制
                    String isfactoryacceptance = getCellValue(row, 28).toString().trim();//是否赴厂验收

                    String isinspection = getCellValue(row, 29).toString().trim();//是否送检
                    String inspectionreq = getCellValue(row, 30).toString().trim();//入所检测要求


                    //判断模板标头是否正确
                    if (i == 3) {
                        if (!"*采购类别".equals(itemtype) || !"物料编码".equals(itemnum) || !"*物料名称".equals(description) || !"规格".equals(specifications) || !"*型号".equals(model)|| !"入所检测要求".equals(inspectionreq)
                                || !"*需求数量".equals(orderqty) || !"*预算单价(元)".equals(unitcost) || !"*是否涉及四新".equals(whfournew)
                                || !"*是否用于危险作业".equals(whethdangerous) || !"是否送检".equals(isinspection) || !"*购置理由".equals(reasonpur)
                                || !"验收要求-点验数量、外观、型号".equals(acceptments) || !"验收要求-查验合格证、出厂质量报告等".equals(acceptments1)
                                || !"验收要求-查验第三方检验报告".equals(acceptments2) || !"验收要求-技术指标验证".equals(acceptments3) || !"验收要求-入所质检".equals(acceptments4)
                                || !"*要求到货日期".equals(reqdeliverydate) || !"*采购方式".equals(purchasemethod) || !"*采购属性".equals(projecttype) || !"*采购渠道".equals(purchaseway)
                                || !"电商采购平台".equals(wheprocuremen) || !"建议电商货号".equals(commerceorder) || !"建议供应商".equals(advisupplier) || !"*紧急程度".equals(urgency)
                                || !"元器件检验技术条件".equals(componentsparts) || !"是否DPA分析".equals(isdpaanalysis) || !"特殊要求(DPA)".equals(specialrequirements)
                                || !"是否特制".equals(isspeciallymade) || !"是否赴厂验收".equals(isfactoryacceptance) || !"*是否本人占用".equals(occupnot)) {
//                            return Result.failed("导入的模板错误，请下载模板进行导入");
                            throw new ServiceException("导入的模板错误，请下载模板进行导入");
                        }
                    } else {

                        int line = i + 1;
                        //校验必填项
                        if (StringUtils.isBlank(itemtype) || StringUtils.isBlank(description) || StringUtils.isBlank(orderqty) || StringUtils.isBlank(unitcost) || StringUtils.isBlank(model)
                                || StringUtils.isBlank(reasonpur) || StringUtils.isBlank(reqdeliverydate) || StringUtils.isBlank(urgency) || StringUtils.isBlank(occupnot)
                                || StringUtils.isBlank(projecttype) || StringUtils.isBlank(purchaseway) || StringUtils.isBlank(purchasemethod) || StringUtils.isBlank(whfournew) || StringUtils.isBlank(whethdangerous)) {
                            StringBuilder stbr = new StringBuilder("第" + line + "行,");
                            stbr.append(StringUtils.isBlank(itemtype) ? "采购类别," : "");
                            stbr.append(StringUtils.isBlank(description) ? "物料名称," : "");
                            stbr.append(StringUtils.isBlank(model) ? "型号," : "");
                            stbr.append(StringUtils.isBlank(orderqty) ? "需求数量," : "");
                            stbr.append(StringUtils.isBlank(unitcost) ? "预算单价(元)," : "");
                            stbr.append(StringUtils.isBlank(reasonpur) ? "购置理由," : "");
                            stbr.append(StringUtils.isBlank(reqdeliverydate) ? "要求到货日期," : "");
                            stbr.append(StringUtils.isBlank(urgency) ? "紧急程度," : "");
                            stbr.append(StringUtils.isBlank(occupnot) ? "是否本人占用," : "");
                            stbr.append(StringUtils.isBlank(projecttype) ? "采购属性," : "");
                            stbr.append(StringUtils.isBlank(purchaseway) ? "采购渠道," : "");
                            stbr.append(StringUtils.isBlank(purchasemethod) ? "采购方式," : "");
                            stbr.append(StringUtils.isBlank(whfournew) ? "是否涉及四新," : "");
                            stbr.append(StringUtils.isBlank(whethdangerous) ? "是否用于危险作业," : "");
                            String result = stbr.substring(0, stbr.lastIndexOf(",")) + "";
                            result = result + "不允许为空";
                            throw new ServiceException(result);
                        }
                        if (StringUtils.isEmpty(itemtype)) {
                            continue;
                        }
                        List<Map<String, Object>> bySql3 = handlerService.findBySql("select itemnum from gq_prline where prid = " + id);
                        if (CollectionUtils.isNotEmpty(bySql3)) {
                            for (Map<String, Object> map : bySql3) {
                                itemnumSet.add(MapUtils.getString(map, "itemnum"));
                            }
                        }
                        //对同一个物料不能重复导入
                        if (CollectionUtils.isNotEmpty(itemnumSet)) {
                            if (itemnumSet.contains(itemnum)) {
                                continue;
                            }
                        }

                        /*
                            物料编码不为空
                        */
                        List<Map<String, Object>> bySql = new ArrayList<>();
                        if (StringUtils.isNotBlank(itemnum)) {
                            bySql = handlerService.findBySql("select * from gq_item where itemnum = '" + itemnum + "'");
                        } else {
                            String sqlAdd = "";
                            String sql = "select * from gq_item where description = '" + description + "' and model = '" + model + "' ";
                            if (StringUtils.isNotBlank(specifications)) {
                                sqlAdd = " and specifications = '" + specifications + "'";
                            }
                            bySql = handlerService.findBySql(sql + sqlAdd);
                        }
                        if (CollectionUtils.isEmpty(bySql)) {
                            //物料编码系统中不存在  并且填写了物料编码再生成
                            if (StringUtils.isNotBlank(description)) {
                                businessExceptionStr = true;
                                noexisItem.put("description", description);
                                noexisItem.put("model", model);
                                noexisItem.put("specifications", specifications);
                                noexisItemList.add(noexisItem);
                                continue;
                            } else {
                                throw new ServiceException("第" + line + "行的物料编码在系统中不存在！");
                            }
                        } else {
                            Map<String, Object> map = bySql.get(0);
                            if (StringUtils.isNotBlank(itemnum)) {
                                description = MapUtils.getString(map, "description");//物料名称
                                model = MapUtils.getString(map, "model");//型号
                                specifications = MapUtils.getString(map, "specifications");//规格
                            }else{
                                itemnum = MapUtils.getString(map, "itemnum");//物料编码
                            }

                            String unit = MapUtils.getString(map, "unit");//计量单位
                            String spemodel = MapUtils.getString(map, "spemodel");//品牌
                            String qualitygrade = MapUtils.getString(map, "qualitygrade");//质量等级
                            String manufacturer = MapUtils.getString(map, "manufacturer");//生产厂家
                            String packform = MapUtils.getString(map, "packform");//封装形式
                            String stexecunical = MapUtils.getString(map, "stexecunical");//执行/技术/标准号
                            String matergrade = MapUtils.getString(map, "matergrade");//材料牌号
                            String maintechtors = MapUtils.getString(map, "maintechtors");//主要技术指标
                            String status = MapUtils.getString(map, "status");//状态
                            String itemid = MapUtils.getString(map, "id");//物料id，跳转用
                            //判断物料主数据是否已启用
                            if ("自由".equals(status)) {
                                throw new ServiceException("第" + line + "行输入物料主数据的状态不是已启用，不能进行导入！");
                            }
                            //校验采购方式
                            StringBuilder sb = new StringBuilder();
                            switch (projecttype) {
                                case "政府采购":
                                    sb.append("'线下采购'");
                                    break;
                                case "非政府采购":
                                    sb.append("'线下采购','电商采购'");
                                    break;
                            }
                            //将采购渠道中-‘线下采购1’改为‘线下采购’
                            if ("线下采购1".equals(purchaseway)) {
                                purchaseway = "线下采购";
                            }
                            List<Map<String, Object>> dictSql = handlerService.findBySql("select code, name from sys_dict where pid in (select id from sys_dict where code = 'po_purchaseway') and  code in (" + sb + ") and code ='" + purchaseway + "'");
                            if (CollectionUtils.isEmpty(dictSql)) {
                                throw new ServiceException("第" + line + "行输入的采购方式不匹配，请检查后重新导入！");
                            }

                            StringBuilder sb1 = new StringBuilder();
                            if ("政府采购".equals(projecttype) && "线下采购".equals(purchaseway)) {
                                sb1.append("'公开招标','邀请招标','竞争性谈判','竞争性磋商','单一来源','询价','其他方式'");
                            } else if ("非政府采购".equals(projecttype)) {
                                switch (purchaseway) {
                                    case "线下采购":
                                        sb1.append("'谈判采购','询比采购','直接采购','小额零星直接实施','小额零星比价实施'");
                                        break;
                                    case "电商采购":
                                        sb1.append("'电商直购','网上比选'");
                                        break;
                                    default:
                                        break;
                                }
                            }
                            List<Map<String, Object>> dictSql1 = handlerService.findBySql("select code, name from sys_dict where " +
                                    " pid in (select id from sys_dict where code = 'po_purchasetype')" +
                                    " and code in (" + sb1 + ") and code = '" + purchasemethod + "'");
                            if (CollectionUtils.isEmpty(dictSql1)) {
                                throw new ServiceException("第" + line + "行输入的采购方式不匹配，请检查后重新导入！");
                            }

                            if (StringUtils.isNotBlank(applyby)) {
                                resultMap.put("applyby", applyby);
                            }
                            //建议供应商
                            if (StringUtils.isNotBlank(advisupplier)) {
                                resultMap.put("advisupplier", advisupplier);
                            }

                            resultMap.put("subjectno", subjectno);//课题号主表带的
                            resultMap.put("itemtype", itemtype);//物料类型
                            resultMap.put("itemnum", itemnum);//物料编码
                            resultMap.put("itemdesc", description);//物料名称
                            resultMap.put("orderunit", unit);//计量单位
                            resultMap.put("spemodel", spemodel);//品牌
                            resultMap.put("itemid", itemid);//物料id 跳转用
                            resultMap.put("qualitygrade", qualitygrade);//质量等级
                            resultMap.put("manufacturer", manufacturer);//生产厂家
                            resultMap.put("packform", packform);//封装形式
                            resultMap.put("model", model);//型号
                            resultMap.put("stexecunical", stexecunical);//执行/技术/标准号
                            resultMap.put("specifications", specifications);//规格
                            resultMap.put("matergrade", matergrade);//材料牌号
                            resultMap.put("maintechtors", maintechtors);//主要技术指标
                            resultMap.put("inspectionreq", inspectionreq);//入所检测要求
                            resultMap.put("orderqty", orderqty);//采购数量
                            resultMap.put("unitcost", unitcost);//单价
                            resultMap.put("urgency", urgency);//紧急程度
                            resultMap.put("occupnot", occupnot);//是否本人占用
                            resultMap.put("componentsparts", componentsparts);//元器件检验技术条件
                            resultMap.put("isdpaanalysis", isdpaanalysis);//是否DPA分析
                            resultMap.put("specialrequirements", specialrequirements);//特殊要求(DPA)
                            resultMap.put("isspeciallymade", isspeciallymade);//是否特制
                            resultMap.put("isfactoryacceptance", isfactoryacceptance);//是否赴厂验收
                            if (StringUtils.isBlank(orderqty) || StringUtils.isBlank(unitcost)) {
                                resultMap.put("linecost", 0);//金额
                            } else {
                                BigDecimal orderqtyBig = new BigDecimal(orderqty);
                                BigDecimal unitcostBig = new BigDecimal(unitcost);
                                BigDecimal multiply = orderqtyBig.multiply(unitcostBig);
//                                Float linecost = Float.parseFloat(orderqty) * Float.parseFloat(unitcost);
                                resultMap.put("linecost", multiply);//金额
                            }
                            resultMap.put("whfournew", whfournew);
                            resultMap.put("whethdangerous", whethdangerous);

                            List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_pr where id=" + id);
                            if (CollectionUtils.isEmpty(bySql1)) {
//                                return Result.failed("这条采购申请不存在!");
                                throw new ServiceException("这条采购申请不存在!");
                            }
                            Map<String, Object> map1 = bySql1.get(0);
                            String prnum = MapUtils.getString(map1, "prnum");//采购申请编号
                            String prid = MapUtils.getString(map1, "id");//采购申请编号
                            resultMap.put("prnum", prnum);
                            resultMap.put("prid", prid);
                            String proid = MapUtils.getString(map1, "proid");//项目id
                            resultMap.put("proid", proid);//项目id

                            if (StringUtils.isNotBlank(isinspection)) {
                                if ("是".equals(isinspection)) {
                                    resultMap.put("isinspection", "是");//是否送检
                                } else {
                                    resultMap.put("isinspection", "否");//是否送检
                                }
                            }
                            resultMap.put("reasonpur", reasonpur);//购置理由

                            yanshouyaoqiu(sheet1, resultMap, acceptments, acceptments1, acceptments2, acceptments3, acceptments4);

                            Cell cell = row.getCell(8);
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            if (cell != null) {
                                try {
                                    double numericCellValue = cell.getNumericCellValue();
                                    Date date = DateUtil.getJavaDate(numericCellValue);
//                                Date date = cell.getDateCellValue();
                                    String format1 = format.format(date);
                                    resultMap.put("reqdeliverydate", format1);//要求到货日期
                                } catch (Exception e) {
                                    throw new ServiceException("第" + line + "行输入的到货日期格式不正确，应设置为单元格格式为yyyy/m/d");
//                                    return Result.failed("第"+line+"行输入的到货日期格式不正确，应设置为单元格格式为yyyy/m/d");
                                }
                            }
                            //校验到货日期不能比当前时间早
                            String format2 = format.format(new Date());
                            if (format2.compareTo(format.format(DateUtil.getJavaDate(cell.getNumericCellValue()))) > 0) {
                                throw new ServiceException("第" + line + "行输入的到货日期小于当前日期！");
                            }
                            resultMap.put("projecttype", projecttype);//采购属性
                            resultMap.put("purchaseway", purchaseway);//采购渠道
                            resultMap.put("purchasemethod", purchasemethod);//采购方式
                            resultMap.put("wheprocuremen", wheprocuremen);//是否位电商采购
                            resultMap.put("commerceorder", commerceorder);//电商订货单

                            //时间，人
                            LoginAppUser user = SysUserUtil.getLoginAppUser();
                            resultMap.put("createuser", user.getId());
                            resultMap.put("updateuser", user.getId());
                            resultMap.put("updatetime", new Date());
                            resultMap.put("createtime", new Date());

                            //是否dpa分析选择是，特殊要求（dpa）必填
                            if (StringUtils.isNotBlank(isdpaanalysis) && "是".equals(isdpaanalysis)) {
                                if (StringUtils.isBlank(specialrequirements)) {
                                    throw new ServiceException("第" + line + "行的DPA分析选择的是，所以该行的特殊要求(DPA)是必填的。");
//                                    return Result.failed("第"+line+"行的DPA分析选择的是，所以该行的特殊要求(DPA)是必填的。");
                                }
                            }
                            //采购类别选择的是A1类，入所检测要求是必填的
                            if (StringUtils.isNotBlank(itemtype) && "A1类(实验用)".equals(itemtype)) {
                                if (StringUtils.isBlank(inspectionreq)) {
                                    throw new ServiceException("第" + line + "行的采购类别为A1类(实验用)，所以该行的入所检测要求是必填的。");
//                                    return Result.failed("第"+line+"行的采购类别为A1类(实验用)，所以该行的入所检测要求是必填的。");
                                }
                            }

                            //采购渠道为电商采购，电商平台、电商货号不为空
                            if ("电商采购".equals(purchaseway)) {
                                if (StringUtils.isBlank(wheprocuremen)) {
                                    throw new ServiceException("第" + line + "行的采购渠道为电商采购，电商采购平台不能为空。");
                                }
                                if (StringUtils.isBlank(commerceorder)) {
                                    throw new ServiceException("第" + line + "行的采购渠道为电商采购，电商货号不能为空。");
                                }
                            }

                            itemList.add(resultMap);
                            handlerService.insert("gq_prline", resultMap);
                        }

                    }
                }
            }
            if (businessExceptionStr) {
                throw new BusinessException("该导入模板里面存在物料信息在系统中不存在！");
            }

            map3.put("msg", "采购明细行成功导入" + itemList.size() + "条数据");
            map3.put("code", 0);
            map3.put("data", null);
            map3.put("ispopup", false);
            return map3;
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            if (e instanceof BusinessException) {
                map3.put("code", 1);
                map3.put("data", noexisItemList);
                map3.put("message", e.getMessage());
                map3.put("ispopup", true);
            } else {
                map3.put("code", 1);
                map3.put("data", null);
                map3.put("msg", e.getMessage());
                map3.put("ispopup", false);
            }
            return map3;
        }
    }

    //处理验收要求
    private void yanshouyaoqiu(Sheet sheet1, Map<String, Object> resultMap, String acceptments, String acceptments1, String acceptments2, String acceptments3, String acceptments4) {
        //验收要求
        HashMap<String, String> map2 = new HashMap<>();
        HashMap<String, String> acceptmentsSelectBoxMap = new HashMap<>();
        List<Map<String, Object>> bySql2 = handlerService.findBySql("SELECT code,name FROM sys_dict WHERE pid = (select id from sys_dict WHERE code = 'acceptments')");
        if (CollectionUtils.isNotEmpty(bySql2)) {
            for (Map<String, Object> stringObjectMap : bySql2) {
                map2.put("验收要求-" + MapUtils.getString(stringObjectMap, "name"), MapUtils.getString(stringObjectMap, "code"));
                acceptmentsSelectBoxMap.put(MapUtils.getString(stringObjectMap, "code"), MapUtils.getString(stringObjectMap, "name"));
            }
        }
        StringJoiner sj = new StringJoiner(",");
        StringJoiner sjnew = new StringJoiner(",");
        if ("是".equals(acceptments)) {
            String trim = getCellValue(sheet1.getRow(3), 19).toString().trim();
            sj.add(map2.get(trim));
            sjnew.add(acceptmentsSelectBoxMap.get(map2.get(trim)));
        }
        if ("是".equals(acceptments1)) {
            String trim = getCellValue(sheet1.getRow(3), 20).toString().trim();
            sj.add(map2.get(trim));
            sjnew.add(acceptmentsSelectBoxMap.get(map2.get(trim)));
        }
        if ("是".equals(acceptments2)) {
            String trim = getCellValue(sheet1.getRow(3), 21).toString().trim();
            sj.add(map2.get(trim));
            sjnew.add(acceptmentsSelectBoxMap.get(map2.get(trim)));
        }
        if ("是".equals(acceptments3)) {
            String trim = getCellValue(sheet1.getRow(3), 22).toString().trim();
            sj.add(map2.get(trim));
            sjnew.add(acceptmentsSelectBoxMap.get(map2.get(trim)));
        }
        if ("是".equals(acceptments4)) {
            String trim = getCellValue(sheet1.getRow(3), 23).toString().trim();
            sj.add(map2.get(trim));
            sjnew.add(acceptmentsSelectBoxMap.get(map2.get(trim)));
        }
        if (sj != null) {
            resultMap.put("acceptments", sj.toString());//验收要求
        }
        if (sjnew != null) {
            resultMap.put("acceptmentsSelectBox", sjnew.toString());//验收要求(展示)
        }
    }

    private boolean checkNullRow(Row row) {
        boolean rowNull = false;//不是空行
        if(row  == null){
            rowNull = true;
        }else{
            boolean isEmpty = true;
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null && cell.getCellType() != CellType.BLANK) {
                    isEmpty = false;
                    break;
                }
            }
            if (isEmpty) {
                // 行为空
                rowNull = true;
            } else {
                // 行非空
                rowNull = false;
            }
        }
        return rowNull;
    }

    public HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        //查询码值表
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //设置下拉框
        if (cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }


    /**
     * 获取Cell内容
     */
    public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {

                if (cell.getCellTypeEnum()==CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.#########");
                    String cellValueStr = String.valueOf(val);
                    val = df.format(Double.valueOf(cellValueStr));
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }


    /**
     * 增加批注
     * @param row
     * @param col
     * @param poiCell
     * @param poiRow
     * @param columnComment
     */
    private void setCommentsOfPoiCell(int row,int col, Cell poiCell,Row poiRow,String columnComment){
        //增加批注
        if(StringUtils.isNotBlank(columnComment) && !"null".equals(columnComment)){
            ClientAnchor anchor = new HSSFClientAnchor();
            //dx1、dy1、dx2、dy2 四个参数是坐标点
            //dx1、dy1为起始单元格的x、y坐标，dx2、dy2为结束单元格的x、y坐标
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            //col1、row1、col2、row2 四个参数是编辑和显示批注时的大小
            //col1、row1为单元格的起始列、行，col2、row2为单元格的终止列、行
            anchor.setCol1(col);
            anchor.setRow1(row);
            anchor.setCol2(col);
            anchor.setRow2(row);
            Drawing draw = poiRow.getSheet().createDrawingPatriarch();
            Comment commentOfCell = draw.createCellComment(anchor);
            commentOfCell.setString(new HSSFRichTextString(columnComment));
            poiCell.setCellComment(commentOfCell);
        }
    }


    /**
     * 执行明细
     * @param response
     * @throws IOException
     */
    @PostMapping("/purappexlsExportExcel")
    public void purappexlsExportExcel(HttpServletResponse response,@RequestParam Map<String,Object> params) throws IOException {
        try {
            //1.创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFFont redFont = wb.createFont();
            redFont.setColor(Font.COLOR_RED);
            HSSFCellStyle style = wb.createCellStyle();
            style.setFont(redFont);

            String ids = MapUtils.getString(params, "ids");
            HSSFSheet sheet = wb.createSheet("Sheet1");
            HSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("申请单号");
            row.createCell(1).setCellValue("课题号");
            row.createCell(2).setCellValue("申请人");
            row.createCell(3).setCellValue("物料类型");
            row.createCell(4).setCellValue("物料编码");
            row.createCell(5).setCellValue("物料名称");
            row.createCell(6).setCellValue("型号");
            row.createCell(7).setCellValue("规格");
            row.createCell(8).setCellValue("购置理由");
            row.createCell(9).setCellValue("主要技术指标");
            row.createCell(10).setCellValue("封装形式");
            row.createCell(11).setCellValue("质量/机械性能/精度");
            row.createCell(12).setCellValue("生产厂家");
            row.createCell(13).setCellValue("建议供应商");
            row.createCell(14).setCellValue("计量单位");
            row.createCell(15).setCellValue("数量");
            row.createCell(16).setCellValue("单价（元）");
            row.createCell(17).setCellValue("金额（元）");
            row.createCell(18).setCellValue("采购方式");
            row.createCell(19).setCellValue("部门");
            row.createCell(20).setCellValue("申请变更单号");
            row.createCell(21).setCellValue("申请日期");
            row.createCell(22).setCellValue("验收要求");
            row.createCell(23).setCellValue("到货日期");
            row.createCell(24).setCellValue("入库日期");
            row.createCell(25).setCellValue("出库日期");
            row.createCell(26).setCellValue("平台签收状态");
            row.createCell(27).setCellValue("验收报告日期");
            row.createCell(28).setCellValue("发票交接日期");
            row.createCell(29).setCellValue("采购员");
            row.createCell(30).setCellValue("订单执行状态");
            row.createCell(31).setCellValue("计划申报时间");
            row.createCell(32).setCellValue("交货周期");
            row.createCell(33).setCellValue("电商订货号");
            row.createCell(34).setCellValue("订单编号");
            row.createCell(35).setCellValue("下单单价（元）");
            row.createCell(36).setCellValue("下单数量");
            row.createCell(37).setCellValue("下单金额（元）");

            List<Map<String, Object>> allSql = new ArrayList<>();
            if(StringUtils.isNotBlank(ids)){
                allSql = purappexlsController.findAllSql(ids);
            }else{
                allSql = purappexlsController.findAllSql(ids);
            }

            if(CollectionUtils.isNotEmpty(allSql)){
                int rownum=1;
                for (Map<String, Object> map : allSql) {
                    HSSFRow row1 = sheet.createRow(rownum);
                    row1.createCell(0).setCellValue(MapUtils.getString(map, "prnum"));
                    row1.createCell(1).setCellValue(MapUtils.getString(map, "subjectno"));
                    row1.createCell(2).setCellValue(MapUtils.getString(map, "createuserdesc"));
                    row1.createCell(3).setCellValue(MapUtils.getString(map, "itemtype"));
                    row1.createCell(4).setCellValue(MapUtils.getString(map, "itemnum"));
                    row1.createCell(5).setCellValue(MapUtils.getString(map, "itemdesc"));
                    row1.createCell(6).setCellValue(MapUtils.getString(map, "model"));
                    row1.createCell(7).setCellValue(MapUtils.getString(map, "specifications"));
                    row1.createCell(8).setCellValue(MapUtils.getString(map, "reasonpur"));
                    row1.createCell(9).setCellValue(MapUtils.getString(map, "maintechtors"));
                    row1.createCell(10).setCellValue(MapUtils.getString(map, "packform"));
                    row1.createCell(11).setCellValue(MapUtils.getString(map, "qualitygrade"));
                    row1.createCell(12).setCellValue(MapUtils.getString(map, "manufacturer"));
                    row1.createCell(13).setCellValue(MapUtils.getString(map, "advisupplier"));
                    row1.createCell(14).setCellValue(MapUtils.getString(map, "orderunit"));
                    row1.createCell(15).setCellValue(MapUtils.getString(map, "orderqty"));
                    row1.createCell(16).setCellValue(MapUtils.getString(map, "unitcost"));
                    row1.createCell(17).setCellValue(MapUtils.getString(map, "linecost"));
                    row1.createCell(18).setCellValue(MapUtils.getString(map, "purchasemethod"));
                    row1.createCell(19).setCellValue(MapUtils.getString(map, "fullname"));
                    row1.createCell(20).setCellValue(MapUtils.getString(map, "prcnum"));
                    row1.createCell(21).setCellValue(MapUtils.getString(map, "applytime"));
                    row1.createCell(22).setCellValue(MapUtils.getString(map, "acceptmentsSelectBox"));
                    row1.createCell(23).setCellValue(MapUtils.getString(map, "reqdeliverydate"));
                    row1.createCell(24).setCellValue(MapUtils.getString(map, "createtime1"));
                    row1.createCell(25).setCellValue(MapUtils.getString(map, "createtime2"));
                    row1.createCell(26).setCellValue(MapUtils.getString(map, "plasigtus"));
                    row1.createCell(27).setCellValue(MapUtils.getString(map, "dalinancert"));
                    row1.createCell(28).setCellValue(MapUtils.getString(map, "inhandate"));
                    row1.createCell(29).setCellValue(MapUtils.getString(map, "procurexetor"));
                    row1.createCell(30).setCellValue(MapUtils.getString(map, "orderextus"));
                    row1.createCell(31).setCellValue(MapUtils.getString(map, "interdetime"));
                    row1.createCell(32).setCellValue(MapUtils.getString(map, "deliverycycle"));
                    row1.createCell(33).setCellValue(MapUtils.getString(map, "commerceorder"));
                    row1.createCell(34).setCellValue(MapUtils.getString(map, "ponum"));
                    row1.createCell(35).setCellValue(MapUtils.getString(map, "unitcost1"));
                    row1.createCell(36).setCellValue(MapUtils.getString(map, "orderqty1"));
                    row1.createCell(37).setCellValue(MapUtils.getString(map, "amexclutax"));
                    row1.createCell(38).setCellValue(MapUtils.getString(map, "amexclutax"));
                    rownum++;
                }
            }



            //输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            String exportFileName ="采购申请执行明细.xls";
            String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            response.setContentType("application/x-xls");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
